package com.dream.xiaobo.dao.impl;

import com.dream.xiaobo.dao.ProductDao;
import com.dream.xiaobo.entity.Product;
import com.dream.xiaobo.entity.Type;
import com.dream.xiaobo.utils.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author xiaobo
 * @date 2021/8/31 - 13:26
 */
public class ProductDaoImpl implements ProductDao {

    //创建queryRunner实例化对象
    private QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 根据商品类别Id查询此类商品的总数
     * @param tId
     * @return
     * @throws SQLException
     */
    @Override
    public Long selectCountBytId(String tId) throws SQLException {

        //根据商品分类Id查询总数的SQL
        String sql = "SELECT COUNT(p_id) FROM product WHERE t_id = ?";
        //执行SQL
        Object query = queryRunner.query(sql, new ScalarHandler(), tId);
        //类型转换
        Long count = (Long) query;
        //返回商品数量
        return count;
    }

    /**
     * 根据商品类型Id进行分页查询的方法
     * @param tId
     * @param page
     * @param pageSize
     * @return
     * @throws SQLException
     */
    @Override
    public List<Product> selectProductByPage(String tId, Integer page, Integer pageSize) throws SQLException {

        //根据商品类型Id进行分页查询的SQL
        String sql = "SELECT p_id AS pId, p_name AS pName, p_time AS pTime," +
                "p_image AS pImage, p_price AS pPrice, p_state AS pState," +
                "p_info AS pInfo, t_id AS tId FROM product WHERE t_id = ? LIMIT ?,?";
        //执行SQL
        List<Product> list = queryRunner.query(sql, new BeanListHandler<Product>(Product.class),
                tId, (page - 1) * pageSize, pageSize);
        //返回查询信息
        return list;
    }

    /**
     * 根据商品Id查询商品信息
     * @param pId
     * @return
     * @throws SQLException
     */
    @Override
    public Product selectProductByPId(String pId) throws SQLException {

        //根据商品Id查询的SQL
        String sql = "SELECT p_id AS pId, p_name AS pName, p_time AS pTime," +
                "p_image AS pImage, p_price AS pPrice, p_state AS pState," +
                "p_info AS pInfo, t_id AS tId FROM product WHERE p_id = ?";
        //执行SQL
        Product product = queryRunner.query(sql, new BeanHandler<Product>(Product.class), pId);
        //返回查询的信息
        return product;
    }

    @Override
    public List<Product> selectProduct() throws SQLException, InvocationTargetException, IllegalAccessException {

        String sql = "SELECT DISTINCT p.p_id AS pId, p.p_name AS pName, p.p_time AS pTime," +
                "p.p_image AS pImage, p.p_price AS pPrice, p.p_state AS pState," +
                "p.p_info AS pInfo, p.t_id AS tId,t.t_name AS tName,t.t_info AS tInfo " +
                "FROM product p,type t WHERE p.t_id = t.t_id";

        List<Map<String, Object>> lists = queryRunner.query(sql, new MapListHandler());

        List<Product> list = new ArrayList<>();

        for (Map<String, Object> map : lists) {
            Product product = new Product();
            Type type = new Type();

            BeanUtils.populate(product,map);
            BeanUtils.populate(type,map);

            product.setType(type);

            list.add(product);
        }

        return list;
    }

    @Override
    public void insertProduct(Product product) throws SQLException {
        String sql = "INSERT INTO product(p_name,p_time,p_image,p_price,p_state," +
                "p_info,t_id) VALUES(?,?,?,?,?,?,?)";

        queryRunner.update(sql,product.getpName(),product.getpTime(),product.getpImage(),
                product.getpPrice(),product.getpState(),product.getpInfo(),product.gettId());
    }
}
